/*
 * EChequeDB.java
 *
 * Created on June 6, 2007, 6:02 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package eCheque;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DriverManager;

/**
 * 
 * @author Saad
 */

public class EChequeDB {

	private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DATABASE_URL = "jdbc:mysql://db.seng.uvic.ca/se426g01";
	private static final String DATABASE_USERNAME = "se426g01";
	private static final String DATABASE_PASSWORD = "f3JB8fZw";
	private Connection connection = null;
	private Statement sqlStatement = null;
	private int databaseMode;
	private ResultSet resultSet;

	private void connectToDataBase() throws ClassNotFoundException,
			SQLException {
		Class.forName(JDBC_DRIVER);
		connection = DriverManager.getConnection(DATABASE_URL, DATABASE_USERNAME,
				DATABASE_PASSWORD);
	}

	private void closeDataBaseConnection() {
		try {
			if (connection != null) {
				connection.close();
			}
			if (sqlStatement != null) {
				sqlStatement.close();
			}
		} catch (SQLException exp) {}
	}

	private void createStatment() throws SQLException {
		sqlStatement = connection.createStatement();
	}

	private void executeSQLStatment(String statment, int statType)
			throws SQLException {
		if (statType == 0) {
			resultSet = sqlStatement.executeQuery(statment);
		} else if (statType == 1) {
			sqlStatement.executeUpdate(statment);
		}
	}

	public boolean runDB(int mode, String databaseStat) {
		databaseMode = mode;
		boolean flag = false;
		try {
			connectToDataBase();
			createStatment();
			executeSQLStatment(databaseStat, databaseMode);
			flag = true;
		} catch (ClassNotFoundException exp) {
			exp.printStackTrace();
		} catch (SQLException exp) {
			exp.printStackTrace();
		} finally {
			closeDataBaseConnection();
		}
		return flag;
	}

	public boolean runDB(int mode, String databaseStat, double[] balance) {
		databaseMode = mode;
		boolean flag = false;
		try {
			connectToDataBase();
			createStatment();
			executeSQLStatment(databaseStat, databaseMode);
			if (resultSet.next()) {
				balance[0] = resultSet.getDouble(1);
				flag = true;
			} else {
				balance[0] = 0.0;
				flag = false;
			}
		} catch (ClassNotFoundException exp) {
			exp.printStackTrace();
		} catch (SQLException exp) {
			exp.printStackTrace();
		} finally {
			closeDataBaseConnection();
		}
		return flag;
	}

	/**
	 * Execute the provided statement and retrieve any results. This should be
	 * used for SELECT only
	 * 
	 * @param preparedStatement
	 *            Statement to execute
	 * 
	 * @return Results of the query
	 * 
	 * @throws SQLException
	 *             If an error occurred during execution. Note, the statement
	 *             does not need to be closed if an error occurs.
	 */
	public ResultSet executeQuery(PreparedStatement preparedStatement)
			throws SQLException {
		try {
			return preparedStatement.executeQuery();
		} catch (SQLException e) {
			closePreparedStatement(preparedStatement);
			throw e;
		}
	}
	
	/**
	 * Execute the provided statement. This should be used for INSERT, UPDATE,
	 * and DELTE
	 * 
	 * @param preparedStatement
	 *            Statement to execute
	 * 
	 * @throws SQLException
	 *             If the execution fails
	 */
	public int executeUpdate(PreparedStatement preparedStatement)
			throws SQLException {
		return preparedStatement.executeUpdate();
	}

	/**
	 * Create a prepared statement from the given SQL. If this method returns
	 * successfully it is the callers responsibility to close the statement with
	 * a call to {@link #closePreparedStatement(PreparedStatement)}
	 * 
	 * @param sql
	 *            SQL to execute
	 * 
	 * @return Statement which can have parameters bound and be executed
	 * 
	 * @throws SQLException
	 *             If an error occurs while connecting to the database or
	 *             creating the statement
	 */
	public PreparedStatement createPreparedStatement(String sql)
			throws SQLException {
		try {
			connectToDataBase();
			return connection.prepareStatement(sql);
		} catch (Exception e) {
			closeDataBaseConnection();
			throw new SQLException(e);
		}
	}

	/**
	 * Close the prepared statement. This should be called after every
	 * successful call to {@link #createPreparedStatement(String)}
	 * 
	 * @param preparedStatement
	 *            Statement to close
	 */
	public void closePreparedStatement(PreparedStatement preparedStatement) {
		try {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {}
	}

	public boolean runDB(String databaseStat, int mode) {
		databaseMode = mode;
		boolean flag = false;

		try {
			connectToDataBase();
			createStatment();
			executeSQLStatment(databaseStat, databaseMode);
			if (resultSet.next()) {
				flag = true;
			} else {
				flag = false;
			}

		} catch (ClassNotFoundException exp) {
			exp.printStackTrace();
		} catch (SQLException exp) {
			exp.printStackTrace();
		} finally {
			closeDataBaseConnection();
		}
		return flag;
	}

}